﻿using System;
using Microsoft.ApplicationBlocks.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace DataAccess
{
    public class KhoDAO
    {
        private static readonly String URL = ConfigurationSettings.AppSettings["Main.ConnectionString"];
        public KhoDAO()
        {

        }

        public static DataSet GetAll()
        {
            return SqlHelper.ExecuteDataset(URL, CommandType.Text, "SELECT k.*,ncc.TenNhaCungCap,nsp.TenNhomSanPham,dv.TenDonVi FROM Kho k JOIN NhaCungCap ncc ON k.MaNhaCungCap = ncc.MaNhaCungCap JOIN NhomSanPham nsp ON nsp.MaNhomSanPham = k.MaNhomSanPham JOIN DonVi dv ON dv.MaDonVi = k.MaDonVi");
        }
        public static DataSet GetReport()
        {
            return SqlHelper.ExecuteDataset(URL, CommandType.Text, "SELECT COUNT(*) AS TongHang,SUM(SoLuong*GiaSanPham) AS TongTienTrongKho FROM Kho");
        }
        public static void Delete(int id) 
        {
            SqlParameter[] sqlParameter = new SqlParameter[1];
            sqlParameter[0] = new SqlParameter("@MaDonVi", id);
            SqlHelper.ExecuteNonQuery(URL, CommandType.Text, "DELETE FROM DonVi WHERE MaDonVi = @MaDonVi", sqlParameter);
        }

        public static void Insert(string name,int value)
        {
            SqlParameter[] sqlParameter = new SqlParameter[2];
            sqlParameter[0] = new SqlParameter("@TenDonVi", name);
            sqlParameter[1] = new SqlParameter("@GiaTri", value);
            SqlHelper.ExecuteNonQuery(URL, CommandType.Text, "INSERT INTO DonVi VALUES(@TenDonVi,@GiaTri)", sqlParameter);
        }

        public static void Update(int id,string name,int value)
        {
            SqlParameter[] sqlParameter = new SqlParameter[3];
            sqlParameter[0] = new SqlParameter("@TenDonVi", name);
            sqlParameter[1] = new SqlParameter("@GiaTri", value);
            sqlParameter[2] = new SqlParameter("@MaDonVi", id);
            SqlHelper.ExecuteNonQuery(URL, CommandType.Text, "UPDATE DonVi SET TenDonVi = @TenDonVi,GiaTri = @GiaTri WHERE MaDonVi = @MaDonVi", sqlParameter);
        }
        public static DataSet GetBy(string id)
        {
            SqlParameter[] sqlParameter = new SqlParameter[1];
            sqlParameter[0] = new SqlParameter("@MaSanPham", id);
            return SqlHelper.ExecuteDataset(URL, CommandType.Text, "SELECT k.*,ncc.TenNhaCungCap,nsp.TenNhomSanPham,dv.TenDonVi FROM Kho k JOIN NhaCungCap ncc ON k.MaNhaCungCap = ncc.MaNhaCungCap JOIN NhomSanPham nsp ON nsp.MaNhomSanPham = k.MaNhomSanPham JOIN DonVi dv ON dv.MaDonVi = k.MaDonVi WHERE k.MaSanPham = @MaSanPham", sqlParameter);
        }
    }
}
